//
// (C) Copyright 2009 Irantha Suwandarathna (irantha@gmail.com)
// All rights reserved.
//
/* Copyright (c) 1995-2000, The Hypersonic SQL Group.
 * All rights reserved.
 *
 * Redistribution and use _in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions _in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer _in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the Hypersonic SQL Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL THE HYPERSONIC SQL GROUP,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This software consists of voluntary contributions made by many individuals
 * on behalf of the Hypersonic SQL Group.
 *
 *
 * For work added by the HSQL Development Group:
 *
 * Copyright (c) 2001-2008, The HSQL Development Group
 * All rights reserved.
 *
 * Redistribution and use _in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions _in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer _in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the HSQL Development Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */


using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using EffiProzDB.Lib;
using EffiProzDB.Persist;
using EffiProzDB.RowIO;


// fredt@users 20020130 - patch 491987 by jimbag@users - made optional
// fredt@users 20020405 - patch 1.7.0 by fredt - quoted identifiers
// for sql standard quoted identifiers for column and table names and aliases
// applied to different places
// fredt@users 20020225 - patch 1.7.0 - restructuring
// some methods moved from Database.java, some rewritten
// changes to several methods
// fredt@users 20020225 - patch 1.7.0 - ON DELETE CASCADE
// fredt@users 20020225 - patch 1.7.0 - named constraints
// boucherb@users 20020225 - patch 1.7.0 - multi-column primary keys
// fredt@users 20020221 - patch 513005 by sqlbob@users (RMP)
// tony_lai@users 20020820 - patch 595099 - user defined PK name
// tony_lai@users 20020820 - patch 595172 - drop constraint fix
// kloska@users 20021030 - patch 1.7.2 - ON UPDATE CASCADE | SET NULL | SET DEFAULT
// kloska@users 20021112 - patch 1.7.2 - ON DELETE SET NULL | SET DEFAULT
// fredt@users 20021210 - patch 1.7.2 - better ADD / DROP INDEX for non-CACHED tables
// fredt@users 20030901 - patch 1.7.2 - allow multiple nulls for UNIQUE columns
// fredt@users 20030901 - patch 1.7.2 - reworked IDENTITY support
// achnettest@users 20040130 - patch 878288 - bug fix for new indexes _in memory tables by Arne Christensen
// boucherb@users 20040327 - doc 1.7.2 - javadoc updates
// boucherb@users 200404xx - patch 1.7.2 - proper uri for getCatalogName
// fredt@users 20050000 - 1.8.0 updates _in several areas
// fredt@users 20050220 - patch 1.8.0 enforcement of DECIMAL precision/scale

namespace EffiProzDB
{

    /**
     *  Holds the data structures and methods for creation of a database table.
     *
     *
     * Extensively rewritten and extended _in successive versions of HSQLDB.
     *
     * @author Thomas Mueller (Hypersonic SQL Group)
     * @version 1.8.0
     * @since Hypersonic SQL
     */
    public class Table : BaseTable , IComparable<Table>
    {

        // types of table
        public const int SYSTEM_TABLE = 0;
        public const int SYSTEM_SUBQUERY = 1;
        public const int TEMP_TABLE = 2;
        public const int MEMORY_TABLE = 3;
        public const int CACHED_TABLE = 4;
        public const int TEMP_TEXT_TABLE = 5;
        public const int TEXT_TABLE = 6;
        public const int VIEW = 7;

        // boucherb@users - for future implementation of SQL standard INFORMATION_SCHEMA
        public const int SYSTEM_VIEW = 8;

        // main properties
        // boucherb@users - access changed _in support of metadata 1.7.2
        public HashMappedList<string, Column> columnList;                 // columns _in table
        private int[] primaryKeyCols;             // column numbers for primary key
        private int[] primaryKeyTypes;            // types for primary key
        private int[] primaryKeyColsSequence;     // {0,1,2,...}
        public int[] bestRowIdentifierCols;      // column set for best index
        public bool bestRowIdentifierStrict;    // true if it has no nullable column
        public int[] bestIndexForColumn;         // index of the 'best' index for each column
        public Index bestIndex;                  // the best index overall - null if there is no user-defined index
        public int identityColumn;                    // -1 means no such row
        public NumberSequence identitySequence;                  // next value of identity column
        public NumberSequence rowIdSequence;                     // next value of optional rowid

        //// -----------------------------------------------------------------------
       public Constraint[] constraintList;                 // constrainst for the table
       public List<TriggerDef>[] triggerLists;                   // array of trigger lists
       private int[] colTypes;                       // fredt - types of columns
       private int[] colSizes;                       // fredt - copy of SIZE values for columns
       private int[] colScales;                      // fredt - copy of SCALE values for columns
       private bool[] colNullable;                    // fredt - modified copy of isNullable() values
       private Expression[] colDefaults;                 // fredt - expressions of DEFAULT values
       private int[] defaultColumnMap;            // fred - holding 0,1,2,3,...
       private bool hasDefaultValues;            //fredt - shortcut for above
       bool sqlEnforceSize;              // inherited from the database -

       // properties for subclasses
       protected int columnCount;              // inclusive the hidden primary key
       public Database database;
       protected DataFileCache cache;
       public HsqlNameManager.HsqlName tableName;                // SQL name
       private int _tableType;
       protected bool _isReadOnly;
       protected bool _isTemp;
       protected bool _isCached;
       protected bool _isText;
       protected bool _isMemory;
       private bool _isView;
       protected bool _isLogged;
       protected int _indexType;                // fredt - type of index used
       public bool onCommitPreserve;         // for temp tables

            //
        public PersistentStore rowStore;
        public Index[] indexList;                        // vIndex(0) is the primary key index

            /**
             *  Constructor
             *
             * @param  db
             * @param  name
             * @param  type
             * @param  sessionid
             * @exception  HsqlException
             */
        public Table(Database db, HsqlNameManager.HsqlName name, int type)
        {
            database = db;
            sqlEnforceSize = db.sqlEnforceStrictSize;
            identitySequence = new NumberSequence(null, 0, 1, Types.BIGINT);
            rowIdSequence = new NumberSequence(null, 0, 1, Types.BIGINT);

            switch (type)
            {

                case SYSTEM_SUBQUERY:
                    _isTemp = true;
                    _isMemory = true;
                    break;

                case SYSTEM_TABLE:
                    _isMemory = true;
                    break;

                case CACHED_TABLE:
                    if (DatabaseURL.isFileBasedDatabaseType(db.getType()))
                    {
                        cache = db.logger.getCache();
                        _isCached = true;
                        _isLogged = !database.isFilesReadOnly();
                        _indexType = Index.DISK_INDEX;
                        rowStore = new RowStore(this);

                        break;
                    }

                    type = MEMORY_TABLE;
                    _isMemory = true;
                    _isLogged = !database.isFilesReadOnly();
                    break;
                case MEMORY_TABLE:
                    _isMemory = true;
                    _isLogged = !database.isFilesReadOnly();
                    break;

                case TEMP_TABLE:
                    _isMemory = true;
                    _isTemp = true;
                    break;

                case TEMP_TEXT_TABLE:
                    if (!DatabaseURL.isFileBasedDatabaseType(db.getType()))
                    {
                        throw Trace.error(Trace.DATABASE_IS_MEMORY_ONLY);
                    }

                    _isTemp = true;
                    _isText = true;
                    _isReadOnly = true;
                    _indexType = Index.POINTER_INDEX;
                    rowStore = new RowStore(this);
                    break;

                case TEXT_TABLE:
                    if (!DatabaseURL.isFileBasedDatabaseType(db.getType()))
                    {
                        throw Trace.error(Trace.DATABASE_IS_MEMORY_ONLY);
                    }

                    _isText = true;
                    _indexType = Index.POINTER_INDEX;
                    rowStore = new RowStore(this);
                    break;

                case VIEW:
                case SYSTEM_VIEW:
                    _isView = true;
                    break;
            }

            // type may have changed above for CACHED tables
            _tableType = type;
            tableName = name;
            primaryKeyCols = null;
            primaryKeyTypes = null;
            identityColumn = -1;
            columnList = new HashMappedList<string,Column>();
            indexList = new Index[0];
            constraintList = new Constraint[0];
            triggerLists = new List<TriggerDef>[TriggerConstants.NUM_TRIGS];

            // ----------------------------------------------------------------------------
            // akede@users - 1.7.2 patch Files _readonly
            // Changing the mode of the table if necessary
            if (db.isFilesReadOnly() && isFileBased())
            {
                setIsReadOnly(true);
            }

            // ----------------------------------------------------------------------------
        }

        public  bool Equals(Session session, string name)
        {

            /*
                    if (isTemp && (session != null
                                   && session.getId() != ownerSessionId)) {
                        return false;
                    }
            */
            return (tableName.name.Equals(name));
        }


       public override bool Equals(object name)
       {
           if (name is HsqlNameManager.HsqlName)
               return (tableName.Equals(name));
           else if (name is string)
               return (tableName.name.Equals(name));
           else
               return base.Equals(name);
    
       }


       public  int CompareTo(Table obj)
       {
           if (obj is Table)
           {
               Table otherTable = (Table)obj;
               return this.tableName.GetHashCode().CompareTo(otherTable.tableName.GetHashCode());           }
           else
           {
               throw new ArgumentException("Object is not a Table");
           }
       }


        public bool isText()
        {
            return _isText;
        }

        public bool isTemp()
        {
            return _isTemp;
        }

        public bool isReadOnly()
        {
            return isDataReadOnly();
        }

        public bool isView()
        {
            return _isView;
        }

        public int getIndexType()
        {
            return _indexType;
        }

        public int getTableType()
        {
            return _tableType;
        }

        public virtual bool isDataReadOnly()
        {
            return _isReadOnly;
        }

        /**
         * sets the isReadOnly flag, and invalidates the database's system tables as needed
         */
        protected void setIsReadOnly(bool newReadOnly)
        {

            _isReadOnly = newReadOnly;

            database.setMetaDirty(true);
        }

        /**
         * Used by INSERT, DELETE, UPDATE operations
         */
       public virtual void checkDataReadOnly()
        {

            if (isDataReadOnly())
            {
                throw Trace.error(Trace.DATA_IS_READONLY);
            }
        }

        // ----------------------------------------------------------------------------
        // akede@users - 1.7.2 patch Files _readonly
        public virtual void setDataReadOnly(bool value)
        {

            // Changing the Read-Only mode for the table is only allowed if
            // the database can realize it.
            if (!value && database.isFilesReadOnly() && isFileBased())
            {
                throw Trace.error(Trace.DATA_IS_READONLY);
            }

            _isReadOnly = value;
        }

        /**
         * Text or Cached Tables are normally file based
         */
        public bool isFileBased()
        {
            return _isCached || _isText;
        }

        /**
         * For text tables
         */
        public virtual void setDataSource(Session s, string source, bool isDesc,
                                     bool newFile)
        {
            throw (Trace.error(Trace.TABLE_NOT_FOUND));
        }

        /**
         * For text tables
         */
        public virtual string getDataSource()
        {
            return null;
        }

        /**
         * For text tables.
         */
        public virtual bool isDescDataSource()
        {
            return false;
        }

        /**
         * For text tables.
         */
        public virtual void setHeader(string header)
        {
            throw Trace.error(Trace.TEXT_TABLE_HEADER);
        }

        /**
         * For text tables.
         */
        public virtual string getHeader()
        {
            return null;
        }

        /**
         * determines whether the table is actually connected to the underlying data source.
         *
         *  <p>This method is available for text tables only.</p>
         *
         *  @see setDataSource
         *  @see disconnect
         *  @see isConnected
         */
        public virtual bool isConnected()
        {
            return true;
        }

        /**
         * connects the table to the underlying data source.
         *
         *  <p>This method is available for text tables only.</p>
         *
         *  @param session
         *      denotes the current session. Might be <code>null</code>.
         *
         *  @see setDataSource
         *  @see disconnect
         *  @see isConnected
         */
        public virtual void connect(Session session)
        {
            throw Trace.error(Trace.CANNOT_CONNECT_TABLE);
        }

        /**
         * disconnects the table from the underlying data source.
         *
         *  <p>This method is available for text tables only.</p>
         *
         *  @param session
         *      denotes the current session. Might be <code>null</code>.
         *
         *  @see setDataSource
         *  @see connect
         *  @see isConnected
         */
        public virtual void disconnect(Session session)
        {
            throw Trace.error(Trace.CANNOT_CONNECT_TABLE);
        }

        /**
         *  Adds a constraint.
         */
        public void addConstraint(Constraint c)
        {

            constraintList =
                (Constraint[])ArrayUtil.toAdjustedArray(constraintList, c,
                    constraintList.Length, 1);
        }

        /**
         *  Returns the list of constraints.
         */
        public Constraint[] getConstraints()
        {
            return constraintList;
        }

        /**
         *  Returns the primary constraint.
         */
        public Constraint getPrimaryConstraint()
        {
            return primaryKeyCols.Length == 0 ? null
                                              : constraintList[0];
        }

        /** @todo fredt - this can be improved to ignore order of columns in
             * multi-column indexes */

        /**
         *  Returns the index supporting a constraint with the given column signature.
         *  Only Unique constraints are considered.
         */
        public Index getUniqueConstraintIndexForColumns(int[] col)
        {

            if (ArrayUtil.areEqual(getPrimaryIndex().getColumns(), col,
                                   col.Length, true))
            {
                return getPrimaryIndex();
            }

            for (int i = 0, size = constraintList.Length; i < size; i++)
            {
                Constraint c = constraintList[i];

                if (c.getType() != Constraint.UNIQUE)
                {
                    continue;
                }

                if (ArrayUtil.areEqual(c.getMainColumns(), col, col.Length,
                                       true))
                {
                    return c.getMainIndex();
                }
            }

            return null;
        }

        /**
         *  Returns any foreign key constraint equivalent to the column sets
         */
        public Constraint getConstraintForColumns(Table tablemain, int[] colmain,
                                           int[] colref)
        {

            for (int i = 0, size = constraintList.Length; i < size; i++)
            {
                Constraint c = constraintList[i];

                if (c.isEquivalent(tablemain, colmain, this, colref))
                {
                    return c;
                }
            }

            return null;
        }

        /**
         *  Returns any unique constraint equivalent to the column set
         */
        public Constraint getUniqueConstraintForColumns(int[] cols)
        {

            for (int i = 0, size = constraintList.Length; i < size; i++)
            {
                Constraint c = constraintList[i];

                if (c.isEquivalent(cols, Constraint.UNIQUE))
                {
                    return c;
                }
            }

            return null;
        }

        /**
         *  Returns any unique Constraint using this index
         *
         * @param  index
         * @return
         */
        public Constraint getUniqueOrPKConstraintForIndex(Index index)
        {

            for (int i = 0, size = constraintList.Length; i < size; i++)
            {
                Constraint c = constraintList[i];

                if (c.getMainIndex() == index
                        && (c.getType() == Constraint.UNIQUE
                            || c.getType() == Constraint.PRIMARY_KEY))
                {
                    return c;
                }
            }

            return null;
        }

        /**
         *  Returns the next constraint of a given type
         *
         * @param  from
         * @param  type
         * @return
         */
         public int getNextConstraintIndex(int from, int type)
        {

            for (int i = from, size = constraintList.Length; i < size; i++)
            {
                Constraint c = constraintList[i];

                if (c.getType() == type)
                {
                    return i;
                }
            }

            return -1;
        }

        // fredt@users 20020220 - patch 475199 - duplicate column

            /**
             *  Performs the table level checks and adds a column to the table at the
             *  DDL level. Only used at table creation, not at alter column.
             */
           public   void addColumn(Column column)   {

                if (findColumn(column.columnName.name) >= 0) {
                    throw Trace.error(Trace.COLUMN_ALREADY_EXISTS,
                                      column.columnName.name);
                }

                if (column.isIdentity()) {
                    Trace.check(
                        column.getType() == Types.INTEGER
                        || column.getType() == Types.BIGINT, Trace.WRONG_DATA_TYPE,
                            column.columnName.name);
                    Trace.check(identityColumn == -1, Trace.SECOND_PRIMARY_KEY,
                                column.columnName.name);

                    identityColumn = columnCount;
                }

                if (primaryKeyCols != null)
                {
                    Trace.doAssert(false, "Table.addColumn");
                }

                columnList.Add(column.columnName.name, column);

                columnCount++;
            }

            /**
             *  Add a set of columns based on a ResultMetaData
             */
           public void addColumns(Result.ResultMetaData metadata,
                            int count)
            {

                for (int i = 0; i < count; i++)
                {
                    Column column = new Column(
                        database.nameManager.newHsqlName(
                            metadata.colLabels[i], metadata.isLabelQuoted[i]), true,
                                metadata.colTypes[i], metadata.colSizes[i],
                                metadata.colScales[i], false, null);

                    addColumn(column);
                }
            }

            /**
             *  Adds a set of columns based on a compiled Select
             */
            public void addColumns(Select select)
            {

                int colCount = select.iResultLen;

                for (int i = 0; i < colCount; i++)
                {
                    Expression e = select.exprColumns[i];
                    Column column = new Column(
                        database.nameManager.newHsqlName(
                            e.getAlias(), e.isAliasQuoted()), true, e.getDataType(),
                                e.getColumnSize(), e.getColumnScale(), false, null);

                    addColumn(column);
                }
            }

        /**
         *  Returns the HsqlNameManager.HsqlName object fo the table
         */
        public HsqlNameManager.HsqlName getName()
        {
            return tableName;
        }

        public int getId()
        {
            return tableName.GetHashCode();
        }

            /**
             * Changes table name. Used by 'alter table rename to'.
             * Essential to use the existing HsqlNameManager.HsqlName as this is is referenced by
             * intances of Constraint etc.
             */
        public void rename(Session session, string newname,
                     bool isquoted)
        {

            string oldname = tableName.name;

            tableName.rename(newname, isquoted);
            renameTableInCheckConstraints(session, oldname, newname);
        }

        /**
         *  Returns total column counts, including hidden ones.
         */
        int getInternalColumnCount()
        {
            return columnCount;
        }

        /**
         * returns a basic duplicate of the table without the data structures.
         */
        protected virtual Table duplicate()
        {

            Table t = new Table(database, tableName, _tableType);

            t.onCommitPreserve = onCommitPreserve;

            return t;
        }

        /**
         * Match two columns arrays for length and type of columns
         *
         * @param col column array from this Table
         * @param other the other Table object
         * @param othercol column array from the other Table
         * @  if there is a mismatch
         */
       public void checkColumnsMatch(int[] col, Table other,
                               int[] othercol)
        {

            if (col.Length != othercol.Length)
            {
                throw Trace.error(Trace.COLUMN_COUNT_DOES_NOT_MATCH);
            }

            for (int i = 0; i < col.Length; i++)
            {

                // integrity check - should not throw _in normal operation
                if (col[i] >= columnCount || othercol[i] >= other.columnCount)
                {
                    throw Trace.error(Trace.COLUMN_COUNT_DOES_NOT_MATCH);
                }

                if (getColumn(col[i]).getType()
                        != other.getColumn(othercol[i]).getType())
                {
                    throw Trace.error(Trace.COLUMN_TYPE_MISMATCH);
                }
            }
        }

        // fredt@users 20020405 - patch 1.7.0 by fredt - DROP and CREATE INDEX bug

        /**
         * Constraints that need removing are removed outside this method.<br>
         * removeIndex is the index of an index to be removed, _in which case
         * no change is made to columns <br>
         * When withoutindex is null,  adjust {-1 | 0 | +1} indicates if a
         * column is {removed | replaced | added}
         *
         */
       public Table moveDefinition(int[] removeIndex, Column newColumn, int colIndex,
                             int adjust)
        {

            Table tn = duplicate();

            // loop beyond the end _in order to be able to add a column to the end
            // of the list
            for (int i = 0; i < columnCount + 1; i++)
            {
                if (i == colIndex)
                {
                    if (adjust == 0)
                    {
                        if (newColumn != null)
                        {
                            tn.addColumn(newColumn);

                            continue;
                        }
                    }
                    else if (adjust > 0)
                    {
                        tn.addColumn(newColumn);
                    }
                    else if (adjust < 0)
                    {
                        continue;
                    }
                }

                if (i == columnCount)
                {
                    break;
                }

                tn.addColumn(getColumn(i));
            }

            // treat it the same as new table creation and
            int[] primarykey = primaryKeyCols.Length == 0 ? null
                                                          : primaryKeyCols;

            if (primarykey != null)
            {
                int[] newpk = ArrayUtil.toAdjustedColumnArray(primarykey,
                    colIndex, adjust);

                if (primarykey.Length != newpk.Length)
                {
                    throw Trace.error(Trace.DROP_PRIMARY_KEY);
                }
                else
                {
                    primarykey = newpk;
                }
            }

            tn.createPrimaryKey(getIndex(0).getName(), primarykey, false);

            tn.constraintList = constraintList;

            Index idx = null;

            if (removeIndex != null)
            {
                idx = getIndex(removeIndex, colIndex);
            }

            if (idx != null)
            {
                if (idx.isConstraint())
                {
                    throw Trace.error(Trace.COLUMN_IS_IN_CONSTRAINT);
                }
                else
                {
                    throw Trace.error(Trace.COLUMN_IS_IN_INDEX);
                }
            }

            for (int i = 1; i < indexList.Length; i++)
            {
                if (removeIndex != null && ArrayUtil.find(removeIndex, i) != -1)
                {
                    continue;
                }

                tn.createAdjustedIndex(indexList[i], colIndex, adjust);
            }

            tn.triggerLists = triggerLists;

            return tn;
        }

        public Index getIndex(int[] exclude, int colIndex) {

               for (int i = 1; i < indexList.Length; i++)
               {
                   if (exclude != null && ArrayUtil.find(exclude, i) != -1)
                   {
                       continue;
                   }

                   Index idx = indexList[i];
                   int[] cols = idx.getColumns();

                   if (ArrayUtil.find(cols, colIndex) != -1)
                   {
                       return idx;
                   }
               }

                return null;
            }

        private void copyIndexes(Table tn, int removeIndex, int colIndex,
                                 int adjust)
        {

            for (int i = 1; i < getIndexCount(); i++)
            {
                Index idx = indexList[i];

                if (removeIndex == i)
                {
                    continue;
                }

                Index newidx = tn.createAdjustedIndex(idx, colIndex, adjust);

                if (newidx == null)
                {

                    // column to remove is part of an index
                    throw Trace.error(Trace.COLUMN_IS_IN_INDEX);
                }
            }
        }

        /**
         * cols == null means drop
         */
        public Table moveDefinitionPK(int[] pkCols,
                               bool withIdentity)
        {

            // some checks
            if ((hasPrimaryKey() && pkCols != null)
                    || (!hasPrimaryKey() && pkCols == null))
            {
                throw Trace.error(Trace.DROP_PRIMARY_KEY);
            }

            Table tn = duplicate();

            for (int i = 0; i < columnCount; i++)
            {
                tn.addColumn(getColumn(i).duplicate(withIdentity));
            }

            tn.createPrimaryKey(getIndex(0).getName(), pkCols, true);

            tn.constraintList = constraintList;

            for (int i = 1; i < getIndexCount(); i++)
            {
                Index idx = getIndex(i);

                tn.createAdjustedIndex(idx, -1, 0);
            }

            tn.triggerLists = triggerLists;

            return tn;
        }

        /**
         * Updates the constraint and replaces references to the old table with
         * the new one, adjusting column index arrays by the given amount.
         */
        public void updateConstraintsTables(Session session, Table old, int colindex,
                                     int adjust)
        {

            for (int i = 0, size = constraintList.Length; i < size; i++)
            {
                Constraint c = constraintList[i];

                c.replaceTable(old, this, colindex, adjust);

                if (c.constType == Constraint.CHECK)
                {
                    recompileCheckConstraint(session, c);
                }
            }
        }

        private void recompileCheckConstraints(Session session)
        {

            for (int i = 0, size = constraintList.Length; i < size; i++)
            {
                Constraint c = constraintList[i];

                if (c.constType == Constraint.CHECK)
                {
                    recompileCheckConstraint(session, c);
                }
            }
        }

        /**
         * Used after adding columns or indexes to the table.
         */
        private void recompileCheckConstraint(Session session,
                                              Constraint c)
        {

            string ddl = c.core.check.getDDL();
            Tokenizer tokenizer = new Tokenizer(ddl);
            Parser parser = new Parser(session, database, tokenizer);
            Expression condition = parser.parseExpression();

            c.core.check = condition;

            // this workaround is here to stop LIKE optimisation (for proper scripting)
            condition.setLikeOptimised();

            Select s = Expression.getCheckSelect(session, this, condition);

            c.core.checkFilter = s.tFilter[0];

            c.core.checkFilter.setAsCheckFilter();

            c.core.mainTable = this;
        }

        /**
         * Used for drop column.
         */
        public void checkColumnInCheckConstraint(string colname)
        {

            for (int i = 0, size = constraintList.Length; i < size; i++)
            {
                Constraint c = constraintList[i];

                if (c.constType == Constraint.CHECK)
                {
                    if (c.hasColumn(this, colname))
                    {
                        throw Trace.error(Trace.COLUMN_IS_REFERENCED, c.getName());
                    }
                }
            }
        }

            /**
             * Used for retype column. Checks whether column is _in an FK or is
             * referenced by a FK
             * @param colIndex index
             */
           public void checkColumnInFKConstraint(int colIndex)   {

                for (int i = 0, size = constraintList.Length; i < size; i++) {
                    Constraint c = constraintList[i];

                    if (c.hasColumn(colIndex)
                            && (c.getType() == Constraint.MAIN
                                || c.getType() == Constraint.FOREIGN_KEY)) {
                        throw Trace.error(Trace.COLUMN_IS_REFERENCED,
                                          c.getName().name);
                    }
                }
            }

            /**
             * Used for column defaults and nullability. Checks whether column is _in an FK.
             * @param colIndex index of column
             * @param refOnly only check FK columns, not referenced columns
             */
             public void checkColumnInFKConstraint(int colIndex,
                                           int actionType)   {

                for (int i = 0, size = constraintList.Length; i < size; i++) {
                    Constraint c = constraintList[i];

                    if (c.hasColumn(colIndex)) {
                        if (c.getType() == Constraint.FOREIGN_KEY
                                && (actionType == c.getUpdateAction()
                                    || actionType == c.getDeleteAction())) {
                            throw Trace.error(Trace.COLUMN_IS_REFERENCED,
                                              c.getName().name);
                        }
                    }
                }
            }

            /**
             * Used for rename column.
             */
            private void renameColumnInCheckConstraints(string oldname,
                    string newname, bool isquoted)   {

                for (int i = 0, size = constraintList.Length; i < size; i++) {
                    Constraint c = constraintList[i];

                    if (c.constType == Constraint.CHECK) {
                        Expression.Collector coll = new Expression.Collector();

                        coll.addAll(c.core.check, Expression.COLUMN);

                        foreach (var e in coll) {                
                            if (e.getColumnName() == oldname) {
                                e.setColumnName(newname, isquoted);
                            }
                        }
                    }
                }
            }

            /**
             * Used for drop column.
             */
            private void renameTableInCheckConstraints(Session session,
                    string oldname, string newname)   {

                for (int i = 0, size = constraintList.Length; i < size; i++) {
                    Constraint c = constraintList[i];

                    if (c.constType == Constraint.CHECK) {
                        Expression.Collector coll = new Expression.Collector();

                        coll.addAll(c.core.check, Expression.COLUMN);

                    

                        foreach (var e in coll)
                        {          

                            if (e.getTableName() == oldname) {
                                e.setTableName(newname);
                            }
                        }
                    }
                }

                recompileCheckConstraints(session);
            }

        /**
         *  Returns the count of user defined columns.
         */
        public int getColumnCount()
        {
            return columnCount;
        }

        /**
         *  Returns the count of indexes on this table.
         */
        public int getIndexCount()
        {
            return indexList.Length;
        }

            /**
             *  Returns the identity column or null.
             */
           public   int getIdentityColumn() {
                return identityColumn;
            }

        /**
         *  Returns the index of given column name or throws if not found
         */
        public int getColumnNr(string c)
        {

            int i = findColumn(c);

            if (i == -1)
            {
                throw Trace.error(Trace.COLUMN_NOT_FOUND, c);
            }

            return i;
        }

            /**
             *  Returns the index of given column name or -1 if not found.
             */
          public   int findColumn(string c) {

                int index = columnList.Keys.IndexOf(c);

                return index;
            }

            /**
             *  Returns the primary index (user defined or system defined)
             */
            public override Index getPrimaryIndex() {
                return getIndex(0);
            }

        /**
         *  Return the user defined primary key column indexes, or empty array for system PK's.
         */
        public int[] getPrimaryKey()
        {
            return primaryKeyCols;
        }

        public int[] getPrimaryKeyTypes()
        {
            return primaryKeyTypes;
        }

        public bool hasPrimaryKey()
        {
            return !(primaryKeyCols.Length == 0);
        }

            public  int[] getBestRowIdentifiers() {
                return bestRowIdentifierCols;
            }

           public   bool isBestRowIdentifiersStrict() {
                return bestRowIdentifierStrict;
            }

            /**
             * This method is called whenever there is a change to table structure and
             * serves two porposes: (a) to reset the best set of columns that identify
             * the rows of the table (b) to reset the best index that can be used
             * to find rows of the table given a column value.
             *
             * (a) gives most weight to a primary key index, followed by a unique
             * address with the lowest count of nullable columns. Otherwise there is
             * no best row identifier.
             *
             * (b) finds for each column an index with a corresponding first column.
             * It uses any type of visible index and accepts the first one (it doesn't
             * make any difference to performance).
             *
             * bestIndex is the user defined, primary key, the first unique index, or
             * the first non-unique index. NULL if there is no user-defined index.
             *
             */
            public  void setBestRowIdentifiers() {

                int[]   briCols      = null;
                int     briColsCount = 0;
                bool isStrict     = false;
                int     nNullCount   = 0;

                // ignore if called prior to completion of primary key construction
                if (colNullable == null) {
                    return;
                }

                bestIndex          = null;
                bestIndexForColumn = new int[columnList.Count];

                ArrayUtil.fillArray(bestIndexForColumn, -1);

                for (int i = 0; i < indexList.Length; i++) {
                    Index index     = indexList[i];
                    int[] cols      = index.getColumns();
                    int   colsCount = index.getVisibleColumns();

                    if (i == 0) {

                        // ignore system primary keys
                        if (hasPrimaryKey()) {
                            isStrict = true;
                        } else {
                            continue;
                        }
                    }

                    if (bestIndexForColumn[cols[0]] == -1) {
                        bestIndexForColumn[cols[0]] = i;
                    }

                    if (!index.isUnique()) {
                        if (bestIndex == null) {
                            bestIndex = index;
                        }

                        continue;
                    }

                    int nnullc = 0;

                    for (int j = 0; j < colsCount; j++) {
                        if (!colNullable[cols[j]]) {
                            nnullc++;
                        }
                    }

                    if (bestIndex != null) {
                        bestIndex = index;
                    }

                    if (nnullc == colsCount) {
                        if (briCols == null || briColsCount != nNullCount
                                || colsCount < briColsCount) {

                            //  nothing found before ||
                            //  found but has null columns ||
                            //  found but has more columns than this index
                            briCols      = cols;
                            briColsCount = colsCount;
                            nNullCount   = colsCount;
                            isStrict     = true;
                        }

                        continue;
                    } else if (isStrict) {
                        continue;
                    } else if (briCols == null || colsCount < briColsCount
                               || nnullc > nNullCount) {

                        //  nothing found before ||
                        //  found but has more columns than this index||
                        //  found but has fewer not null columns than this index
                        briCols      = cols;
                        briColsCount = colsCount;
                        nNullCount   = nnullc;
                    }
                }

                // remove rowID column from bestRowIdentiferCols
                bestRowIdentifierCols = briCols == null
                                        || briColsCount == briCols.Length ? briCols
                                                                          : ArrayUtil
                                                                          .arraySlice(briCols,
                                                                              0, briColsCount);
                bestRowIdentifierStrict = isStrict;

                if (hasPrimaryKey()) {
                    bestIndex = getPrimaryIndex();
                }
            }

            /**
             * Sets the SQL default value for a columm.
             */
            public void setDefaultExpression(int columnIndex, Expression def)
            {

                Column column = getColumn(columnIndex);

                column.setDefaultExpression(def);

                colDefaults[columnIndex] = column.getDefaultExpression();

                resetDefaultsFlag();
            }

            /**
             * sets the flag for the presence of any default expression
             */
            public void resetDefaultsFlag()
            {

                hasDefaultValues = false;

                for (int i = 0; i < columnCount; i++) {
                    hasDefaultValues = hasDefaultValues || colDefaults[i] != null;
                }
            }

            public DataFileCache getCache() {
                return cache;
            }

            /**
             *  Used _in TableFilter to get an index for the column.
             *  An index is created automatically for system tables or subqueries.
             */
            public Index getIndexForColumn(Session session, int column)
            {

                int i = bestIndexForColumn[column];

                if (i == -1
                        && (_tableType == Table.SYSTEM_SUBQUERY
                            || _tableType == Table.SYSTEM_TABLE)) {
                    try {
                        HsqlNameManager.HsqlName indexName = database.nameManager.newAutoName("IDX");

                        createIndex(session, new int[]{ column }, indexName, false,
                                    false, false);

                        i = bestIndexForColumn[column];
                    } catch (Exception ) {}
                }

                return i == -1 ? null
                               : getIndex(i);
            }

            /**
             *  Used for TableFilter to get an index for the columns
             */
            Index getIndexForColumns(bool[] columnCheck) {

                Index indexChoice = null;
                int   colCount    = 0;

                for (int i = 0; i < indexList.Length; i++) {
                    Index index = indexList[i];
                    bool result = ArrayUtil.containsAllTrueElements(columnCheck,
                        index.colCheck);

                    if (result && index.getVisibleColumns() > colCount) {
                        colCount    = index.getVisibleColumns();
                        indexChoice = index;
                    }
                }

                return indexChoice;
            }

            /**
             *  Finds an existing index for a foreign key column group
             */
            Index getIndexForColumns(int[] col, bool unique)   {

                for (int i = 0, count = getIndexCount(); i < count; i++) {
                    Index currentindex = getIndex(i);
                    int[] indexcol     = currentindex.getColumns();

                    if (ArrayUtil.haveEqualArrays(indexcol, col, col.Length)) {
                        if (!unique || currentindex.isUnique()) {
                            return currentindex;
                        }
                    }
                }

                return null;
            }

            /**
             *  Return the list of file pointers to root nodes for this table's
             *  indexes.
             */
            public int[] getIndexRootsArray() {

                int[] roots = new int[getIndexCount()];

                for (int i = 0; i < getIndexCount(); i++) {
                    roots[i] = indexList[i].getRoot();
                }

                return roots;
            }

            /**
             * Returns the string consisting of file pointers to roots of indexes
             * plus the next identity value (hidden or user defined). This is used
             * with CACHED tables.
             */
            public string getIndexRoots()
            {

                string       roots = StringUtil.getList(getIndexRootsArray(), " ", "");
                StringBuilder s     = new StringBuilder(roots);

                s.Append(' ');
                s.Append(identitySequence.peek());

                return s.ToString();
            }

            /**
             *  Sets the index roots of a cached/text table to specified file
             *  pointers. If a
             *  file pointer is -1 then the particular index root is null. A null index
             *  root signifies an empty table. Accordingly, all index roots should be
             *  null or all should be a valid file pointer/reference.
             */
            public  void setIndexRoots(int[] roots)   {

                Trace.check(_isCached, Trace.TABLE_NOT_FOUND);

                for (int i = 0; i < getIndexCount(); i++) {
                    int p = roots[i];
                    Row r = null;

                    if (p != -1) {
                        r = (CachedRow) rowStore.get(p);
                    }

                    Node f = null;

                    if (r != null) {
                        f = r.getNode(i);
                    }

                    indexList[i].setRoot(null, f);
                }
            }

            /**
             *  Sets the index roots and next identity.
             */
           public virtual void setIndexRoots(string s)   {

                // the user may try to set this; this is not only internal problem
                Trace.check(_isCached, Trace.TABLE_NOT_FOUND);

                Tokenizer t     = new Tokenizer(s);
                int[]     roots = new int[getIndexCount()];

                for (int i = 0; i < getIndexCount(); i++) {
                    int v = t.getInt();

                    roots[i] = v;
                }

                setIndexRoots(roots);

                long v1 = t.getBigint();

                identitySequence.reset(v1);
            }

        /**
         *  Shortcut for creating system table PK's.
         */
        public void createPrimaryKey(int[] cols)
        {
            createPrimaryKey(null, cols, false);
        }

            /**
             *  Shortcut for creating default PK's.
             */
          public  void createPrimaryKey()   {
                createPrimaryKey(null, null, false);
            }

            /**
             *  Creates a single or multi-column primary key and index. sets the
             *  colTypes array. Finalises the creation of the table. (fredt@users)
             */

        // tony_lai@users 20020820 - patch 595099
            public void createPrimaryKey(HsqlNameManager.HsqlName indexName, int[] columns,
                                  bool columnsNotNull)   {

                if (primaryKeyCols != null) {
                    Trace.doAssert(false, "Table.createPrimaryKey(column)");
                }

                if (columns == null) {
                    columns = new int[0];
                } else {
                    for (int i = 0; i < columns.Length; i++) {
                        if (columnsNotNull) {
                            getColumn(columns[i]).setNullable(false);
                        }

                        getColumn(columns[i]).setPrimaryKey(true);
                    }
                }

                primaryKeyCols   = columns;
                colTypes         = new int[columnCount];
                colDefaults      = new Expression[columnCount];
                colSizes         = new int[columnCount];
                colScales        = new int[columnCount];
                colNullable      = new bool[columnCount];
                defaultColumnMap = new int[columnCount];

                for (int i = 0; i < columnCount; i++) {
                    setColumnTypeVars(i);
                }

                primaryKeyTypes = new int[primaryKeyCols.Length];

                ArrayUtil.copyColumnValues(colTypes, primaryKeyCols, primaryKeyTypes);

                primaryKeyColsSequence = new int[primaryKeyCols.Length];

                ArrayUtil.fillSequence(primaryKeyColsSequence);
                resetDefaultsFlag();

                // tony_lai@users 20020820 - patch 595099
                HsqlNameManager.HsqlName name = indexName != null ? indexName
                                                  : database.nameManager.newAutoName(
                                                      "IDX");

                createPrimaryIndex(columns, name);
                setBestRowIdentifiers();
            }

            public void setColumnTypeVars(int i)
            {

                Column column = getColumn(i);

                colTypes[i]         = column.getType();
                colSizes[i]         = column.getSize();
                colScales[i]        = column.getScale();
                colNullable[i]      = column.isNullable();
                defaultColumnMap[i] = i;

                if (column.isIdentity()) {
                    identitySequence.reset(column.identityStart,
                                           column.identityIncrement);
                }

                colDefaults[i] = column.getDefaultExpression();
            }

            public HsqlNameManager.HsqlName makeSysPKName()
            {
                return database.nameManager.newAutoName("PK");
            }

            public void createPrimaryIndex(int[] pkcols, HsqlNameManager.HsqlName name)
            {

                int[] pkcoltypes = new int[pkcols.Length];

                for (int j = 0; j < pkcols.Length; j++) {
                    pkcoltypes[j] = colTypes[pkcols[j]];
                }

                Index newindex = new Index(database, name, this, pkcols, pkcoltypes,
                                           true, true, true, false, pkcols,
                                           pkcoltypes, _isTemp);

                addIndex(newindex);
            }

            public override int GetHashCode()
            {
                return base.GetHashCode();
            }

            /**
             *  Create new index taking into account removal or addition of a column
             *  to the table.
             */
            private Index createAdjustedIndex(Index index, int colindex,
                                              int adjust)   {

                int[] indexcolumns = (int[]) ArrayUtil.resizeArray(index.getColumns(),
                    index.getVisibleColumns());
                int[] colarr = ArrayUtil.toAdjustedColumnArray(indexcolumns, colindex,
                    adjust);

                // if a column to remove is one of the Index columns
                if (colarr.Length != index.getVisibleColumns()) {
                    return null;
                }

                return createIndexStructure(colarr, index.getName(), index.isUnique(),
                                            index._isConstraint, index._isForward);
            }

            /**
             *  Create new memory-resident index. For MEMORY and TEXT tables.
             */
            public Index createIndex(Session session, int[] column, HsqlNameManager.HsqlName name,
                              bool unique, bool constraint,
                              bool forward)   {

                int newindexNo = createIndexStructureGetNo(column, name, unique,
                    constraint, forward);
                Index         newindex     = indexList[newindexNo];
                Index         primaryindex = getPrimaryIndex();
                RowIterator   it           = primaryindex.firstRow(session);
                int           rowCount     = 0;
                HsqlException error        = null;

                try {
                    while (it.hasNext()) {
                        Row  row      = it.next();
                        Node backnode = row.getNode(newindexNo - 1);
                        Node newnode  = Node.newNode(row, newindexNo, this);

                        newnode.nNext  = backnode.nNext;
                        backnode.nNext = newnode;

                        // count before inserting
                        rowCount++;

                        newindex.insert(session, row, newindexNo);
                    }

                    return newindex;
                } catch (OutOfMemoryException) {
                    error = Trace.error(Trace.OUT_OF_MEMORY);
                } catch (HsqlException e) {
                    error = e;
                }

                // backtrack on error
                // rowCount rows have been modified
                it = primaryindex.firstRow(session);

                for (int i = 0; i < rowCount; i++) {
                    Row  row      = it.next();
                    Node backnode = row.getNode(0);
                    int  j        = newindexNo;

                    while (--j > 0) {
                        backnode = backnode.nNext;
                    }

                    backnode.nNext = backnode.nNext.nNext;
                }

                indexList = (Index[]) ArrayUtil.toAdjustedArray(indexList, null,
                        newindexNo, -1);

                setBestRowIdentifiers();

                throw error;
            }

            /**
             * Creates the internal structures for an index.
             */
            public Index createIndexStructure(int[] columns, HsqlNameManager.HsqlName name, bool unique,
                                       bool constraint,
                                       bool forward)   {

                int i = createIndexStructureGetNo(columns, name, unique, constraint,
                                                  forward);

                return indexList[i];
            }

            public int createIndexStructureGetNo(int[] column, HsqlNameManager.HsqlName name, bool unique,
                                          bool constraint,
                                          bool forward)   {

                if (primaryKeyCols == null) {
                    Trace.doAssert(false, "createIndex");
                }

                int   s    = column.Length;
                int[] col  = new int[s];
                int[] type = new int[s];

                for (int j = 0; j < s; j++) {
                    col[j]  = column[j];
                    type[j] = colTypes[col[j]];
                }

                int[] pkcols  = getPrimaryKey();
                int[] pktypes = getPrimaryKeyTypes();
                Index newindex = new Index(database, name, this, col, type, false,
                                           unique, constraint, forward, pkcols,
                                           pktypes, _isTemp);
                int indexNo = addIndex(newindex);

                setBestRowIdentifiers();

                return indexNo;
            }

            private int addIndex(Index index) {

                int i = 0;

                for (; i < indexList.Length; i++) {
                    Index current = indexList[i];
                    int order = index.getIndexOrderValue()
                                - current.getIndexOrderValue();

                    if (order < 0) {
                        break;
                    }
                }

                indexList = (Index[]) ArrayUtil.toAdjustedArray(indexList, index, i,
                        1);

                return i;
            }

            /**
             * returns false if the table has to be recreated _in order to add / drop
             * indexes. Only CACHED tables return false.
             */
            public virtual bool isIndexingMutable()
            {
                return !isIndexCached();
            }

            /**
             *  Checks for use of a named index _in table constraints,
             *  while ignorring a given set of constraints.
             * @throws  HsqlException if index is used _in a constraint
             */
            public void checkDropIndex(string indexname, HashSet<string> ignore,
                                bool dropPK)   {

                Index index = this.getIndex(indexname);

                if (index == null) {
                    throw Trace.error(Trace.INDEX_NOT_FOUND, indexname);
                }

                if (!dropPK && index.Equals(getIndex(0))) {
                    throw Trace.error(Trace.DROP_PRIMARY_KEY, indexname);
                }

                for (int i = 0, size = constraintList.Length; i < size; i++) {
                    Constraint c = constraintList[i];

                    if (ignore != null && ignore.Contains(c.constName.name)) {
                        continue;
                    }

                    if (c.isIndexFK(index)) {
                        throw Trace.error(Trace.DROP_FK_INDEX, indexname);
                    }

                    if (c.isIndexUnique(index)) {
                        throw Trace.error(Trace.SYSTEM_INDEX, indexname);
                    }
                }

                return;
           }

            /**
             *  Returns true if the table has any rows at all.
             */
            public bool isEmpty(Session session)
            {

                if (getIndexCount() == 0)
                {
                    return true;
                }

                return getIndex(0).isEmpty(session);
            }

            /**
             * Returns direct mapping array.
             */
            public int[] getColumnMap()
            {
                return defaultColumnMap;
            }

            public IList<Column> getColumnList()
            {
                return columnList.Values;
            }

            /**
             * Returns empty mapping array.
             */
            public int[] getNewColumnMap()
            {
                return new int[columnCount];
            }

            /**
             * Returns empty bool array.
             */
            public bool[] getNewColumnCheckList()
            {
                return new bool[columnCount];
            }

            /**
             * Returns empty Object array for a new row.
             */
            public Object[] getEmptyRowData() {
                return new Object[columnCount];
            }

            /**
             * Returns array for a new row with SQL DEFAULT value for each column n
             * where exists[n] is false. This provides default values only where
             * required and avoids evaluating these values where they will be
             * overwritten.
             */
            public Object[] getNewRowData(Session session,
                                   bool[] exists)   {

                Object[] data = new Object[columnCount];
                int      i;

                if (exists != null && hasDefaultValues) {
                    for (i = 0; i < columnCount; i++) {
                        Expression def = colDefaults[i];

                        if (exists[i] == false && def != null) {
                            data[i] = def.getValue(session, colTypes[i]);
                        }
                    }
                }

                return data;
            }

            /**
             *  Performs Table structure modification and changes to the index nodes
             *  to remove a given index from a MEMORY or TEXT table. Not for PK index.
             *
             */
           public  void dropIndex(Session session, string indexname)   {

                // find the array index for indexname and remove
                int todrop = getIndexIndex(indexname);

                indexList = (Index[]) ArrayUtil.toAdjustedArray(indexList, null,
                        todrop, -1);

                setBestRowIdentifiers();
                dropIndexFromRows(session, todrop);
            }

            public void dropIndexFromRows(Session session, int index)   {

                RowIterator it = getPrimaryIndex().firstRow(session);

                while (it.hasNext()) {
                    Row  row      = it.next();
                    int  i        = index - 1;
                    Node backnode = row.getNode(0);

                    while (i-- > 0) {
                        backnode = backnode.nNext;
                    }

                    backnode.nNext = backnode.nNext.nNext;
                }
            }

            /**
             * Moves the data from table to table.
             * The colindex argument is the index of the column that was
             * added or removed. The adjust argument is {-1 | 0 | +1}
             */
            public void moveData(Session session, Table from, int colindex,
                          int adjust)   {

                Object colvalue = null;
                Column column   = null;

                if (adjust >= 0 && colindex != -1) {
                    column   = getColumn(colindex);
                    colvalue = column.getDefaultValue(session);
                }

                RowIterator it = from.getPrimaryIndex().firstRow(session);

                while (it.hasNext()) {
                    Row      row  = it.next();
                    Object[] o    = row.getData();
                    Object[] data = getEmptyRowData();

                    if (adjust == 0 && colindex != -1) {
                        colvalue = Column.convertObject(session, o[colindex],
                                                        column.getType(),
                                                        column.getSize(),
                                                        column.getScale());
                    }

                    ArrayUtil.copyAdjustArray(o, data, colvalue, colindex, adjust);
                    setIdentityColumn(session, data);
                    enforceNullConstraints(data);

                    Row newrow = newRow(data);

                    indexRow(session, newrow);
                }

                from.drop();
            }

            /**
             *  Highest level multiple row insert method. Corresponds to an SQL
             *  INSERT INTO ... SELECT ... statement.
             */
           public  int insert(Session session, Result ins)   {

                Record ni    = ins.rRoot;
                int    count = 0;

                fireAll(session, TriggerConstants.INSERT_BEFORE);

                while (ni != null) {
                    insertRow(session, ni.data);

                    ni = ni.next;

                    count++;
                }

                fireAll(session, TriggerConstants.INSERT_AFTER);

                return count;
            }

            /**
             *  Highest level method for inserting a single row. Corresponds to an
             *  SQL INSERT INTO .... VALUES(,,) statement.
             *  fires triggers.
             */
           public  void insert(Session session, Object[] data)   {

                fireAll(session, TriggerConstants.INSERT_BEFORE);
                insertRow(session, data);
                fireAll(session, TriggerConstants.INSERT_AFTER);
            }

            /**
             *  Mid level method for inserting rows. Performs constraint checks and
             *  fires row level triggers.
             */
            private void insertRow(Session session,
                                   Object[] data)   {

                                       if (triggerLists[TriggerConstants.INSERT_BEFORE_ROW] != null)
                                       {
                                           fireAll(session, TriggerConstants.INSERT_BEFORE_ROW, null, data);
                }

                setIdentityColumn(session, data);
                checkRowDataInsert(session, data);
                insertNoCheck(session, data);

                if (triggerLists[TriggerConstants.INSERT_AFTER_ROW] != null)
                {
                    fireAll(session, TriggerConstants.INSERT_AFTER_ROW, null, data);
                    checkRowDataInsert(session, data);
                }
            }

            /**
             * Multi-row insert method. Used for SELECT ... INTO tablename queries.
             * These tables are new, empty tables, with no constraints, triggers
             * column default values, column size enforcement whatsoever.
             *
             * Not used for INSERT INTO .... SELECT ... FROM queries
             */
            public  void insertIntoTable(Session session, Result result)   {

                insertResult(session, result);

                if (!_isLogged) {
                    return;
                }

                Record r = result.rRoot;

                while (r != null) {
                    database.logger.writeInsertStatement(session, this, r.data);

                    r = r.next;
                }
            }

            /**
             *  Low level method for row insert.
             *  UNIQUE or PRIMARY constraints are enforced by attempting to
             *  add the row to the indexes.
             */
            private void insertNoCheck(Session session,
                                       Object[] data)   {

                Row row = newRow(data);

                // this handles the UNIQUE constraints
                indexRow(session, row);

                if (session != null) {
                    session.addInsertAction(this, row);
                }

                if (_isLogged) {
                    database.logger.writeInsertStatement(session, this, data);
                }
            }

            /**
             *
             */
            public void insertNoCheckFromLog(Session session,
                                             Object[] data)   {

                Row r = newRow(data);

                updateIdentityValue(data);
                indexRow(session, r);

                if (session != null) {
                    session.addInsertAction(this, r);
                }
            }

            /**
             *  Low level method for restoring deleted rows
             */
           public void insertNoCheckRollback(Session session, Row row,
                                       bool log)   {

                Row newrow = restoreRow(row);

                // instead of new row, use new routine so that the row does not use
                // rowstore.add(), which will allocate new space and different pos
                indexRow(session, newrow);

                if (log && _isLogged) {
                    database.logger.writeInsertStatement(session, this, row.getData());
                }
             }

            /**
             * Used for system table inserts. No checks. No identity
             * columns.
             */
            public int insertSys(Result ins)   {

                Record ni    = ins.rRoot;
                int    count = 0;

                while (ni != null) {
                    insertData(null, ni.data);

                    ni = ni.next;

                    count++;
                }

                return count;
            }

            /**
             * Used for subquery inserts. No checks. No identity
             * columns.
             */
           public int insertResult(Session session, Result ins)   {

                Record ni    = ins.rRoot;
                int    count = 0;

                while (ni != null) {
                    Object[] newData =
                        (Object[]) ArrayUtil.resizeArrayIfDifferent(ni.data,
                            columnCount);

                    insertData(session, newData);

                    ni = ni.next;

                    count++;
                }

                return count;
            }

           /**
            * Not for general use.
            * Used by ScriptReader to unconditionally insert a row into
            * the table when the .script file is read.
            */
           public void insertFromScript(Object[] data)
           {
               updateIdentityValue(data);
               insertData(null, data);
           }

            /**
             * Used by the methods above.
             */
            public void insertData(Session session,
                                   Object[] data)   {

                Row row = newRow(data);

                indexRow(session, row);
                commitRowToStore(row);
            }

            /**
             * Used by the system tables
             */
            public void insertSys(Object[] data)   {

                Row row = newRow(data);

                indexRow(null, row);
            }

            /**
             * Used by TextCache to insert a row into the indexes when the source
             * file is first read.
             */
            protected void insertFromTextSource(CachedRow row)   {

                Object[] data = row.getData();

                updateIdentityValue(data);
                enforceFieldValueLimits(data, defaultColumnMap);
                enforceNullConstraints(data);
                indexRow(null, row);
            }

            /**
             * Checks a row against NOT NULL constraints on columns.
             */
            protected void enforceNullConstraints(Object[] data)   {

                for (int i = 0; i < columnCount; i++) {
                    if (data[i] == null && !colNullable[i]) {
                        Trace.throwerror(Trace.TRY_TO_INSERT_NULL,
                                         "column: " + getColumn(i).columnName.name
                                         + " table: " + tableName.name);
                    }
                }
            }

            /**
             * If there is an identity column (visible or hidden) on the table, sets
             * the value and/or adjusts the iIdentiy value for the table.
             */
            protected void setIdentityColumn(Session session,
                                             Object[] data)   {

                if (identityColumn != -1) {
                    long id = 0; 

                    if (data[identityColumn] == null)
                    {
                        if (colTypes[identityColumn] == Types.INTEGER) {
                            id =  identitySequence.getValue();
                            data[identityColumn] = (int)id;
                        } else {
                            id = identitySequence.getValue();
                            data[identityColumn] = (long)id;
                        }
                       
                    } else {
                        id = Convert.ToInt64(data[identityColumn]);
                        identitySequence.getValue((long)id );
                    }

                    if (session != null) {
                        session.setLastIdentity((int)id);
                    }
                }
            }

            /**
             * If there is an identity column (visible or hidden) on the table, sets
             * the max identity value.
             */
            protected void updateIdentityValue(Object[] data)   {

                if (identityColumn != -1) {
                    object id =  data[identityColumn];

                    if (id != null) {
                        identitySequence.getValue(Convert.ToInt64(id) );
                    }
                }
            }

            /**
             *  Enforce max field sizes according to SQL column definition.
             *  SQL92 13.8
             */
            void enforceFieldValueLimits(Object[] data,
                                         int[] cols)   {

                int i;
                int colindex;

                if (sqlEnforceSize) {
                    if (cols == null) {
                        cols = defaultColumnMap;
                    }

                    for (i = 0; i < cols.Length; i++) {
                        colindex = cols[i];

                        if ((colTypes[colindex] == Types.TIMESTAMP || colSizes[colindex] != 0)
                                && data[colindex] != null) {
                            data[colindex] = Column.enforceSize(data[colindex],
                                                                colTypes[colindex],
                                                                colSizes[colindex],
                                                                colScales[colindex],
                                                                true);
                        }
                    }
                }
            }

        // fredt@users 20020130 - patch 491987 by jimbag@users - modified

            /**
             *  Fires all row-level triggers of the given set (trigger type)
             *
             */
            void fireAll(Session session, int trigVecIndx, Object[] oldrow,
                         Object[] newrow) {

                if (!database.isReferentialIntegrity()) {

                    // isReferentialIntegrity is false when reloading db
                    return;
                }

                List<TriggerDef> trigVec = triggerLists[trigVecIndx];

                if (trigVec == null) {
                    return;
                }

                foreach (var td in trigVec) {
        
                    td.pushPair(session, oldrow, newrow);    // tell the trigger thread to fire with this row
                }
            }

            /**
             *  Statement level triggers.
             */
            public void fireAll(Session session, int trigVecIndex) {

                if (triggerLists[trigVecIndex] != null) {
                    fireAll(session, trigVecIndex, null, null);
                }
            }

            /**
             * Adds a trigger.
             */
            public void addTrigger(TriggerDef trigDef) {

                if (triggerLists[trigDef.vectorIndex] == null) {
                    triggerLists[trigDef.vectorIndex] = new List<TriggerDef>();
                }

                triggerLists[trigDef.vectorIndex].Add(trigDef);
            }

            /**
             * Drops a trigger.
             */
         public void dropTrigger(string name) {

                // look _in each trigger list of each type of trigger
                int numTrigs = TriggerConstants.NUM_TRIGS;

                for (int tv = 0; tv < numTrigs; tv++) {
                    List<TriggerDef> v = triggerLists[tv];
                    
                    if (v == null) {
                        continue;
                    }

                    for (int tr = v.Count - 1; tr >= 0; tr--) {
                        TriggerDef td = (TriggerDef) v[tr];

                        if (td.name.name.Equals(name)) {
                            v.Remove(td);
                            td.terminate();
                        }
                    }

                    if (v.Count == 0) {
                        triggerLists[tv] = null;
                    }
                }
            }

            /**
             * Drops all triggers.
             */
           public void dropTriggers() {

                // look _in each trigger list of each type of trigger
                int numTrigs = TriggerConstants.NUM_TRIGS;

                for (int tv = 0; tv < numTrigs; tv++) {
                    List<TriggerDef> v = triggerLists[tv];

                    if (v == null) {
                        continue;
                    }

                    for (int tr = v.Count - 1; tr >= 0; tr--) {
                        TriggerDef td = (TriggerDef) v[tr];

                        td.terminate();
                    }

                    triggerLists[tv] = null;
                }
            }

            /** @todo fredt - reused structures to be reviewed for multi-threading */

            /**
             * Reusable set of all FK constraints that have so far been enforced while
             * a cascading insert or delete is _in progress. This is emptied and passed
             * with the first call to checkCascadeDelete or checkCascadeUpdate. During
             * recursion, if an FK constraint is encountered and is already present
             * _in the set, the recursion stops.
             */
        HashSet<Constraint> constraintPath;

            /**
             * Current list of updates on this table. This is emptied once a cascading
             * operation is over.
             */
        HashMappedList<Table, HashMappedList<Row, object[]>> tableUpdateList;

        // fredt@users 20020225 - patch 1.7.0 - CASCADING DELETES

            /**
             *  Method is called recursively on a tree of tables from the current one
             *  until no referring foreign-key table is left. In the process, if a
             *  non-cascading foreign-key referring table contains data, an exception
             *  is thrown. Parameter delete indicates whether to delete refering rows.
             *  The method is called first to check if the row can be deleted, then to
             *  delete the row and all the refering rows.<p>
             *
             *  Support added for SET NULL and SET DEFAULT by kloska@users involves
             *  switching to checkCascadeUpdate(,,,,) when these rules are encountered
             *  _in the constraint.(fredt@users)
             *
             * @table  table table to update
             * @param  tableUpdateLists list of update lists
             * @param  row row to delete
             * @param  session
             * @param  delete
             * @param  path
             * @throws  HsqlException
             */
            static void checkCascadeDelete(Session session, Table table,
                                           HashMappedList<Table, HashMappedList<Row, object[]>> tableUpdateLists, Row row,
                                           bool delete,
                                           HashSet<Constraint> path)   {

                for (int i = 0, size = table.constraintList.Length; i < size; i++) {
                    Constraint c = table.constraintList[i];

                    if (c.getType() != Constraint.MAIN || c.getRef() == null) {
                        continue;
                    }

                    RowIterator refiterator = c.findFkRef(session, row.getData(),
                                                          delete);

                    if (!refiterator.hasNext()) {
                        continue;
                    }

                    try {
                        if (c.core.deleteAction == Constraint.NO_ACTION) {
                            if (c.core.mainTable == c.core.refTable) {
                                Row refrow = refiterator.next();

                                // fredt - it's the same row
                                // this supports deleting a single row
                                // _in future we can iterate over and check against
                                // the full delete row list to enable multi-row
                                // with self-referencing FK's deletes
                                if (row.Equals(refrow)) {
                                    continue;
                                }
                            }

                            throw Trace.error(Trace.INTEGRITY_CONSTRAINT_VIOLATION,
                                              Trace.Constraint_violation,
                                              new Object[] {
                                c.core.fkName.name, c.core.refTable.getName().name
                            });
                        }

                        Table reftable = c.getRef();

                        // shortcut when deltable has no imported constraint
                        bool hasref =
                            reftable.getNextConstraintIndex(0, Constraint.MAIN) != -1;

                        // if (reftable == this) we don't need to go further and can return ??
                        if (delete == false && hasref == false) {
                            continue;
                        }

                        Index    refindex  = c.getRefIndex();
                        int[]    m_columns = c.getMainColumns();
                        int[]    r_columns = c.getRefColumns();
                        Object[] mdata     = row.getData();
                        bool isUpdate = c.getDeleteAction() == Constraint.SET_NULL
                                           || c.getDeleteAction()
                                              == Constraint.SET_DEFAULT;

                        // -- list for records to be inserted if this is
                        // -- a 'ON DELETE SET [NULL|DEFAULT]' constraint
                        HashMappedList<Row, object[]> rowSet = null;

                        if (isUpdate) {
                            rowSet = null;

                            if (!tableUpdateLists.TryGetValue(reftable, out rowSet))
                            {
                                rowSet = new HashMappedList<Row, object[]>();

                                tableUpdateLists.Add(reftable, rowSet);
                            }
                        }

                        // walk the index for all the nodes that reference delnode
                        for (;;) {
                            Row refrow = refiterator.next();

                            if (refrow == null || refrow.isCascadeDeleted()
                                    || refindex.compareRowNonUnique(
                                        session, mdata, m_columns,
                                        refrow.getData()) != 0) {
                                break;
                            }

                            // -- if the constraint is a 'SET [DEFAULT|NULL]' constraint we have to keep
                            // -- a new record to be inserted after deleting the current. We also have to
                            // -- switch over to the 'checkCascadeUpdate' method below this level
                            if (isUpdate) {
                                Object[] rnd = reftable.getEmptyRowData();

                                Array.Copy(refrow.getData(), 0, rnd, 0,
                                                 rnd.Length);

                                if (c.getDeleteAction() == Constraint.SET_NULL) {
                                    for (int j = 0; j < r_columns.Length; j++) {
                                        rnd[r_columns[j]] = null;
                                    }
                                } else {
                                    for (int j = 0; j < r_columns.Length; j++) {
                                        Column col = reftable.getColumn(r_columns[j]);

                                        rnd[r_columns[j]] =
                                            col.getDefaultValue(session);
                                    }
                                }

                                if (hasref && path.Add(c)) {

                                    // fredt - avoid infinite recursion on circular references
                                    // these can be rings of two or more mutually dependent tables
                                    // so only one visit per constraint is allowed
                                    checkCascadeUpdate(session, reftable, null,
                                                       refrow, rnd, r_columns, null,
                                                       path);
                                    path.Remove(c);
                                }

                                if (delete) {

                                    //  foreign key referencing own table - do not update the row to be deleted
                                    if (reftable != table || !refrow.Equals(row)) {
                                        mergeUpdate(rowSet, refrow, rnd, r_columns);
                                    }
                                }
                            } else if (hasref) {
                                if (reftable != table) {
                                    if (path.Add(c)) {
                                        checkCascadeDelete(session, reftable,
                                                           tableUpdateLists, refrow,
                                                           delete, path);
                                        path.Remove(c);
                                    }
                                } else {

                                    // fredt - we avoid infinite recursion on the fk's referencing the same table
                                    // but chained rows can result _in very deep recursion and StackOverflowError
                                    if (refrow != row) {
                                        checkCascadeDelete(session, reftable,
                                                           tableUpdateLists, refrow,
                                                           delete, path);
                                    }
                                }
                            }

                            if (delete && !isUpdate && !refrow.isCascadeDeleted()) {
                                reftable.deleteNoRefCheck(session, refrow);
                            }
                        }
                    } finally {
                        refiterator.release();
                    }
                }
            }

            /**
             * Check or perform an update cascade operation on a single row.
             * Check or cascade an update (delete/insert) operation.
             * The method takes a pair of rows (new data,old data) and checks
             * if Constraints permit the update operation.
             * A bool arguement determines if the operation should
             * realy take place or if we just have to check for constraint violation.
             * fredt - cyclic conditions are now avoided by checking for second visit
             * to each constraint. The set of list of updates for all tables is passed
             * and filled _in recursive calls.
             *
             *   @param session current database session
             *   @param table
             *   @param tableUpdateLists lists of updates
             *   @param orow old row data to be deleted.
             *   @param nrow new row data to be inserted.
             *   @param cols indices of the columns actually changed.
             *   @param ref This should be initialized to null when the
             *   method is called from the 'outside'. During recursion this will be the
             *   current table (i.e. this) to indicate from where we came.
             *   Foreign keys to this table do not have to be checked since they have
             *   triggered the update and are valid by definition.
             *
             *   @short Check or perform and update cascade operation on a single row.
             *
             *
             */
            static void checkCascadeUpdate(Session session, Table table,
                                           HashMappedList<Table, HashMappedList<Row, object[]>> tableUpdateLists, Row orow,
                                           Object[] nrow, int[] cols, Table _ref,
                                           HashSet<Constraint> path)   {

                // -- We iterate through all constraints associated with this table
                // --
                for (int i = 0, size = table.constraintList.Length; i < size; i++) {
                    Constraint c = table.constraintList[i];

                    if (c.getType() == Constraint.FOREIGN_KEY && c.getRef() != null) {

                        // -- (1) If it is a foreign key constraint we have to check if the
                        // --     main table still holds a record which allows the new values
                        // --     to be set _in the updated columns. This test however will be
                        // --     skipped if the reference table is the main table since changes
                        // --     _in the reference table triggered the update and therefor
                        // --     the referential integrity is guaranteed to be valid.
                        // --
                        if (_ref == null || c.getMain() != _ref) {

                            // -- common indexes of the changed columns and the main/ref constraint
                            if (ArrayUtil.countCommonElements(cols, c.getRefColumns())
                                    == 0) {

                                // -- Table::checkCascadeUpdate -- NO common cols; reiterating
                                continue;
                            }

                            c.hasMainRef(session, nrow);
                        }
                    } else if (c.getType() == Constraint.MAIN && c.getRef() != null) {

                        // -- (2) If it happens to be a main constraint we check if the slave
                        // --     table holds any records refering to the old contents. If so,
                        // --     the constraint has to support an 'on update' action or we
                        // --     throw an exception (all via a call to Constraint.findFkRef).
                        // --
                        // -- If there are no common columns between the reference constraint
                        // -- and the changed columns, we reiterate.
                        int[] common = ArrayUtil.commonElements(cols,
                            c.getMainColumns());

                        if (common == null) {

                            // -- NO common cols between; reiterating
                            continue;
                        }

                        int[] m_columns = c.getMainColumns();
                        int[] r_columns = c.getRefColumns();

                        // fredt - find _out if the FK columns have actually changed
                        bool nochange = true;

                        for (int j = 0; j < m_columns.Length; j++) {
                            if (!orow.getData()[m_columns[j]].Equals(
                                    nrow[m_columns[j]])) {
                                nochange = false;

                                break;
                            }
                        }

                        if (nochange) {
                            continue;
                        }

                        // there must be no record _in the 'slave' table
                        // sebastian@scienion -- dependent on forDelete | forUpdate
                        RowIterator refiterator = c.findFkRef(session, orow.getData(),
                                                              false);

                        if (refiterator.hasNext()) {
                            if (c.core.updateAction == Constraint.NO_ACTION) {
                                throw Trace.error(Trace.INTEGRITY_CONSTRAINT_VIOLATION,
                                                  Trace.Constraint_violation,
                                                  new Object[] {
                                    c.core.fkName.name, c.core.refTable.getName().name
                                });
                            }
                        } else {

                            // no referencing row found
                            continue;
                        }

                        Table reftable = c.getRef();

                        // -- unused shortcut when update table has no imported constraint
                        bool hasref =
                            reftable.getNextConstraintIndex(0, Constraint.MAIN) != -1;
                        Index refindex = c.getRefIndex();

                        // -- walk the index for all the nodes that reference update node
                        HashMappedList<Row, object[]> rowSet;

                        if (!tableUpdateLists.TryGetValue(reftable,out rowSet))
                        {
                            rowSet = new HashMappedList<Row, object[]>();

                            tableUpdateLists.Add(reftable, rowSet);
                        }

                        for (Row refrow = refiterator.next(); ;
                                refrow = refiterator.next()) {
                            if (refrow == null
                                    || refindex.compareRowNonUnique(
                                        session, orow.getData(), m_columns,
                                        refrow.getData()) != 0) {
                                break;
                            }

                            Object[] rnd = reftable.getEmptyRowData();

                            Array.Copy(refrow.getData(), 0, rnd, 0, rnd.Length);

                            // -- Depending on the type constraint we are dealing with we have to
                            // -- fill up the forign key of the current record with different values
                            // -- And handle the insertion procedure differently.
                            if (c.getUpdateAction() == Constraint.SET_NULL) {

                                // -- set null; we do not have to check referential integrity any further
                                // -- since we are setting <code>null</code> values
                                for (int j = 0; j < r_columns.Length; j++) {
                                    rnd[r_columns[j]] = null;
                                }
                            } else if (c.getUpdateAction() == Constraint.SET_DEFAULT) {

                                // -- set default; we check referential integrity with ref==null; since we manipulated
                                // -- the values and referential integrity is no longer guaranteed to be valid
                                for (int j = 0; j < r_columns.Length; j++) {
                                    Column col = reftable.getColumn(r_columns[j]);

                                    rnd[r_columns[j]] = col.getDefaultValue(session);
                                }

                                if (path.Add(c)) {
                                    checkCascadeUpdate(session, reftable,
                                                       tableUpdateLists, refrow, rnd,
                                                       r_columns, null, path);
                                    path.Remove(c);
                                }
                            } else {

                                // -- cascade; standard recursive call. We inherit values from the foreign key
                                // -- table therefor we set ref==this.
                                for (int j = 0; j < m_columns.Length; j++) {
                                    rnd[r_columns[j]] = nrow[m_columns[j]];
                                }

                                if (path.Add(c)) {
                                    checkCascadeUpdate(session, reftable,
                                                       tableUpdateLists, refrow, rnd,
                                                       common, table, path);
                                    path.Remove(c);
                                }
                            }

                            mergeUpdate(rowSet, refrow, rnd, r_columns);
                        }
                    }
                }
            }

            /**
             *  Merges a triggered change with a previous triggered change, or adds to
             * list.
             */
            public static void mergeUpdate(HashMappedList<Row, object[]> rowSet, Row row, Object[] newData,
                                    int[] cols) {

                Object[] data = null ;

                if (rowSet.TryGetValue(row, out data)) {
                    for (int j = 0; j < cols.Length; j++) {
                        data[cols[j]] = newData[cols[j]];
                    }
                } else {
                    rowSet.Add(row, newData);
                }
            }

            /**
             * Merge the full triggered change with the updated row, or add to list.
             * Return false if changes conflict.
             */
            public static bool mergeKeepUpdate(Session session, HashMappedList<Row, object[]> rowSet,
                                           int[] cols, int[] colTypes, Row row,
                                           Object[] newData)   {

                Object[] data = null;

                if (rowSet.TryGetValue(row, out data))
                {
                    if (Index.compareRows(
                            session, row
                                .getData(), newData, cols, colTypes) != 0 && Index
                                    .compareRows(
                                        session, newData, data, cols, colTypes) != 0) {
                        return false;
                    }

                    for (int j = 0; j < cols.Length; j++) {
                        newData[cols[j]] = data[cols[j]];
                    }

                    rowSet[row] = newData;
                } else {
                    rowSet.Add(row, newData);
                }

                return true;
            }

            public static void clearUpdateLists(HashMappedList<Table, HashMappedList<Row, object[]>> tableUpdateList)
            {

                for (int i = 0; i < tableUpdateList.Count; i++) {
                    HashMappedList<Row, object[]> updateList =
                        (HashMappedList<Row, object[]>)tableUpdateList.Values[i];

                    updateList.Clear();
                }
            }

            /**
             *  Highest level multiple row delete method. Corresponds to an SQL
             *  DELETE.
             */
           public int delete(Session session,
                       List<Row> deleteList)   {

               HashSet<Constraint> path = constraintPath == null ? new HashSet<Constraint>()
                                          : constraintPath;

                constraintPath = null;

                HashMappedList<Table, HashMappedList<Row, object[]>> tUpdateList = tableUpdateList == null
                                             ? new HashMappedList<Table, HashMappedList<Row, object[]>>()
                                             : tableUpdateList;

                tableUpdateList = null;

                if (database.isReferentialIntegrity()) {
                    for (int i = 0; i < deleteList.Count; i++) {
                        Row row = (Row) deleteList[i];

                        path.Clear();
                        checkCascadeDelete(session, this, tUpdateList, row, false,
                                           path);
                    }
                }

                // check transactions
                database.txManager.checkDelete(session, deleteList);

                for (int i = 0; i < tUpdateList.Count; i++) {
                    Table          table      = (Table) tUpdateList.Keys[i];
                    HashMappedList<Row, object[]> updateList =tUpdateList.Values[i];

                    database.txManager.checkDelete(session, updateList);
                }

                // perform delete
                fireAll(session, TriggerConstants.DELETE_BEFORE);

                if (database.isReferentialIntegrity()) {
                    for (int i = 0; i < deleteList.Count; i++) {
                        Row row = (Row) deleteList[i];

                        path.Clear();
                        checkCascadeDelete(session, this, tUpdateList, row, true,
                                           path);
                    }
                }

                for (int i = 0; i < deleteList.Count; i++) {
                    Row row = (Row) deleteList[i];

                    if (!row.isCascadeDeleted()) {
                        deleteNoRefCheck(session, row);
                    }
                }

                for (int i = 0; i < tUpdateList.Count; i++) {
                    Table table = (Table)tUpdateList.Keys[i];
                    HashMappedList<Row, object[]> updateList = tUpdateList.Values[i];

                    table.updateRowSet(session, updateList, null, false);
                    updateList.Clear();
                }

                fireAll(session, TriggerConstants.DELETE_AFTER);
                path.Clear();

                constraintPath  = path;
                tableUpdateList = tUpdateList;

                return deleteList.Count;
            }

            /**
             *  Mid level row delete method. Fires triggers but no integrity
             *  constraint checks.
             */
            private void deleteNoRefCheck(Session session,
                                          Row row)   {

                Object[] data = row.getData();

                fireAll(session, TriggerConstants.DELETE_BEFORE_ROW, data, null);
                deleteNoCheck(session, row, true);

                // fire the delete after statement trigger
                fireAll(session, TriggerConstants.DELETE_AFTER_ROW, data, null);
            }

            /**
             * Low level row delete method. Removes the row from the indexes and
             * from the Cache.
             */
            private void deleteNoCheck(Session session, Row row,
                                       bool log)   {

                if (row.isCascadeDeleted()) {
                    return;
                }

                Object[] data = row.getData();

                row = row.getUpdatedRow();

                for (int i = indexList.Length - 1; i >= 0; i--) {
                    Node node = row.getNode(i);

                    indexList[i].delete(session, node);
                }

                row.delete();

                if (session != null) {
                    session.addDeleteAction(this, row);
                }

                if (log && _isLogged) {
                    database.logger.writeDeleteStatement(session, this, data);
                }
            }

            /**
             * For log statements.
             */
            public void deleteNoCheckFromLog(Session session,
                                             Object[] data)   {

                Row row = null;

                if (hasPrimaryKey()) {
                    RowIterator it = getPrimaryIndex().findFirstRow(session, data,
                        primaryKeyColsSequence);

                    row = it.next();
                } else if (bestIndex == null) {
                    RowIterator it = getPrimaryIndex().firstRow(session);

                    while (true) {
                        row = it.next();

                        if (row == null) {
                            break;
                        }

                        if (Index.compareRows(
                                session, row.getData(), data, defaultColumnMap,
                                colTypes) == 0) {
                            break;
                        }
                    }
                } else {
                    RowIterator it = bestIndex.findFirstRow(session, data);

                    while (true) {
                        row = it.next();

                        if (row == null) {
                            break;
                        }

                        Object[] rowdata = row.getData();

                        // reached end of range
                        if (bestIndex.compareRowNonUnique(
                                session, data, bestIndex.getColumns(), rowdata) != 0) {
                            row = null;

                            break;
                        }

                        if (Index.compareRows(
                                session, rowdata, data, defaultColumnMap,
                                colTypes) == 0) {
                            break;
                        }
                    }
                }

                if (row == null) {
                    return;
                }

                // not necessary for log deletes
                database.txManager.checkDelete(session, row);

                for (int i = indexList.Length - 1; i >= 0; i--) {
                    Node node = row.getNode(i);

                    indexList[i].delete(session, node);
                }

                row.delete();

                if (session != null) {
                    session.addDeleteAction(this, row);
                }
            }

            /**
             * Low level row delete method. Removes the row from the indexes and
             * from the Cache. Used by rollback.
             */
            public void deleteNoCheckRollback(Session session, Row row,
                                       bool log)   {

                row = indexList[0].findRow(session, row);

                for (int i = indexList.Length - 1; i >= 0; i--) {
                    Node node = row.getNode(i);

                    indexList[i].delete(session, node);
                }

                row.delete();
                removeRowFromStore(row);

                if (log && _isLogged) {
                    database.logger.writeDeleteStatement(session, this, row.getData());
                }
            }

            /**
             * Highest level multiple row update method. Corresponds to an SQL
             * UPDATE. To DEAL with unique constraints we need to perform all
             * deletes at once before the inserts. If there is a UNIQUE constraint
             * violation limited only to the duration of updating multiple rows,
             * we don't want to abort the operation. Example:
             * UPDATE MYTABLE SET UNIQUECOL = UNIQUECOL + 1
             * After performing each cascade update, delete the main row.
             * After all cascade ops and deletes have been performed, insert new
             * rows.
             *
             * The following clauses from SQL Standard section 11.8 are enforced
             * 9) Let ISS be the innermost SQL-statement being executed.
             * 10) If evaluation of these General Rules during the execution of ISS
             * would cause an update of some site to a value that is distinct from the
             * value to which that site was previously updated during the execution of
             * ISS, then an exception condition is raised: triggered data change
             * violation.
             * 11) If evaluation of these General Rules during the execution of ISS
             * would cause deletion of a row containing a site that is identified for
             * replacement _in that row, then an exception condition is raised:
             * triggered data change violation.
             *
             *  (fredt)
             */
            public int update(Session session, HashMappedList<Row, object[]> updateList,
                       int[] cols)   {

                           HashSet<Constraint> path = constraintPath == null ? new HashSet<Constraint>()
                                                      : constraintPath;

                constraintPath = null;

                HashMappedList<Table, HashMappedList<Row, object[]>> tUpdateList = tableUpdateList == null
                                             ? new HashMappedList<Table, HashMappedList<Row, object[]>>()
                                             : tableUpdateList;

                tableUpdateList = null;

                // set identity column where null and check columns
                for (int i = 0; i < updateList.Count; i++) {
                    Object[] data = (Object[]) updateList.Values[i];

                    // this means the identity column can be set to null to force
                    // creation of a new identity value
                    setIdentityColumn(session, data);
                    enforceFieldValueLimits(data, cols);
                    enforceNullConstraints(data);
                }

                // perform check/cascade operations
                if (database.isReferentialIntegrity()) {
                    for (int i = 0; i < updateList.Count; i++) {
                        Object[] data = (Object[]) updateList.Values[i];
                        Row      row  = (Row) updateList.Keys[i];

                        checkCascadeUpdate(session, this, tUpdateList, row, data,
                                           cols, null, path);
                    }
                }

                fireAll(session, TriggerConstants.UPDATE_BEFORE);

                // merge any triggered change to this table with the update list
                HashMappedList<Row, object[]> triggeredList = null; 

                if (tUpdateList.TryGetValue(this , out triggeredList))
                {
                    for (int i = 0; i < triggeredList.Count; i++) {
                        Row      row  = (Row) triggeredList.Keys[i];
                        Object[] data = (Object[]) triggeredList.Values[i];

                        mergeKeepUpdate(session, updateList, cols, colTypes, row,
                                        data);
                    }

                    triggeredList.Clear();
                }

                // check transactions
                for (int i = 0; i < tUpdateList.Count; i++) {
                    Table          table       = (Table) tUpdateList.Keys[(i)];
                    HashMappedList<Row, object[]> updateListT = (HashMappedList<Row, object[]>)tUpdateList.Values[(i)];

                    database.txManager.checkDelete(session, updateListT);
                }

                database.txManager.checkDelete(session, updateList);

                // update lists - main list last
                for (int i = 0; i < tUpdateList.Count; i++) {
                    Table          table       = (Table) tUpdateList.Keys[i];
                    HashMappedList<Row, object[]> updateListT = (HashMappedList<Row, object[]>)tUpdateList.Values[i];

                    table.updateRowSet(session, updateListT, null, false);
                    updateListT.Clear();
                }

                updateRowSet(session, updateList, cols, true);
                fireAll(session, TriggerConstants.UPDATE_AFTER);
                path.Clear();

                constraintPath  = path;
                tableUpdateList = tUpdateList;

                clearUpdateLists(tableUpdateList);

                return updateList.Count;
            }

            public void updateRowSet(Session session, HashMappedList<Row, object[]> rowSet, int[] cols,
                              bool nodelete)   {

                for (int i = rowSet.Count - 1; i >= 0; i--) {
                    Row      row  = (Row) rowSet.Keys[(i)];
                    Object[] data = (Object[])rowSet.Values[(i)];

                    if (row.isCascadeDeleted()) {
                        if (nodelete) {
                            throw Trace.error(Trace.TRIGGERED_DATA_CHANGE);
                        } else {
                            rowSet.Remove(row);

                            continue;
                        }
                    }

                    for (int j = 0; j < constraintList.Length; j++) {
                        Constraint c = constraintList[j];

                        if (c.getType() == Constraint.CHECK) {
                            c.checkCheckConstraint(session, data);

                            continue;
                        }
                    }

                    deleteNoCheck(session, row, true);
                }

                for (int i = 0; i < rowSet.Count; i++) {
                    Row row = (Row)rowSet.Keys[(i)];
                    Object[] data = (Object[])rowSet.Values[(i)];

                    if (triggerLists[TriggerConstants.UPDATE_BEFORE_ROW] != null)
                    {
                        fireAll(session, TriggerConstants.UPDATE_BEFORE_ROW, row.getData(),
                                data);
                        checkRowDataUpdate(session, data, cols);
                    }

                    insertNoCheck(session, data);

                    if (triggerLists[TriggerConstants.UPDATE_AFTER_ROW] != null) {
                        fireAll(session, TriggerConstants.UPDATE_AFTER_ROW, row.getData(),
                                data);
                        checkRowDataUpdate(session, data, cols);
                    }
                }
            }

            public void checkRowDataInsert(Session session,
                                    Object[] data)   {

                enforceFieldValueLimits(data, null);
                enforceNullConstraints(data);

                if (database.isReferentialIntegrity()) {
                    for (int i = 0, size = constraintList.Length; i < size; i++) {
                        constraintList[i].checkInsert(session, data);
                    }
                }
            }

            public void checkRowDataUpdate(Session session, Object[] data,
                                    int[] cols)   {

                enforceFieldValueLimits(data, cols);
                enforceNullConstraints(data);

                for (int j = 0; j < constraintList.Length; j++) {
                    Constraint c = constraintList[j];

                    if (c.getType() == Constraint.CHECK) {
                        c.checkCheckConstraint(session, data);
                    }
                }
            }

            /**
             *  True if table is CACHED or TEXT
             *
             * @return
             */
           public   bool isCached() {
                return _isCached;
            }

            /**
             *  Returns true if table is CACHED
             */
            public virtual bool isIndexCached() {
                return _isCached;
            }

            /**
             * Returns the index of the Index object of the given name or -1 if not found.
             */
            public int getIndexIndex(string indexName) {

                Index[] indexes = indexList;

                for (int i = 0; i < indexes.Length; i++) {
                    if (indexName.Equals(indexes[i].getName().name)) {
                        return i;
                    }
                }

                // no such index
                return -1;
            }

            /**
             * Returns the Index object of the given name or null if not found.
             */
           public Index getIndex(string indexName) {

                Index[] indexes = indexList;
                int     i       = getIndexIndex(indexName);

                return i == -1 ? null
                               : indexes[i]; //TODOx
     
            }

            /**
             *  Return the position of the constraint within the list
             */
            public int getConstraintIndex(string constraintName) {

                for (int i = 0, size = constraintList.Length; i < size; i++) {
                    if (constraintList[i].getName().name.Equals(constraintName)) {
                        return i;
                    }
                }

                return -1;
            }

            /**
             *  return the named constriant
             */
            public Constraint getConstraint(string constraintName) {

                int i = getConstraintIndex(constraintName);

                return (i < 0) ? null
                               : (Constraint) constraintList[i]; 
   
            }

            /**
             * remove a named constraint
             */
            public void removeConstraint(string name) {

                int index = getConstraintIndex(name);

                constraintList =
                    (Constraint[]) ArrayUtil.toAdjustedArray(constraintList, null,
                        index, -1);
            }

            /**
             *  Returns the Column object at the given index
             */
            public  Column getColumn(int i)
            {
                return (Column)columnList.Values[(i)];
            }

            public void renameColumn(Column column, string newName,
                              bool isquoted)   {

                string oldname = column.columnName.name;
                int    i       = getColumnNr(oldname);

                columnList.Keys[i] = newName;             
              
                column.columnName.rename(newName, isquoted);
                renameColumnInCheckConstraints(oldname, newName, isquoted);
            }

        /**
         *  Returns an array of int valuse indicating the SQL type of the columns
         */
        public int[] getColumnTypes()
        {
            return colTypes;
        }

        /**
         *  Returns the Index object at the given index
         */
        public Index getIndex(int i)
        {
            return indexList[i];
        }

        public Index[] getIndexes()
        {
            return indexList;
        }

           /**
             *  Used by CACHED tables to fetch a Row from the Cache, resulting _in the
             *  Row being read from disk if it is not _in the Cache.
             *
             *  TEXT tables pass the memory resident Node parameter so that the Row
             *  and its index Nodes can be relinked.
             */
            public CachedRow getRow(int pos, Node primarynode)   {

                if (_isText) {
                    CachedDataRow row = (CachedDataRow) rowStore.get(pos);

                    row.nPrimaryNode = primarynode;

                    return row;
                } else if (_isCached) {
                    return (CachedRow) rowStore.get(pos);
                }

                return null;
            }

            /**
             * As above, only for CACHED tables
             */
            public CachedRow getRow(int pos)
            {
                return (CachedRow)rowStore.get(pos);
            }

            /**
             * As above, only for CACHED tables
             */
            public CachedRow getRow(long id) {
                return (CachedRow) rowStore.get((int) id);
            }

            /**
             * called _in autocommit mode or by transaction manager when a a delete is committed
             */
            public void removeRowFromStore(Row row)   {

                if (_isCached || _isText && cache != null) {
                    rowStore.remove(row.getPos());
                }
            }

           public  void releaseRowFromStore(Row row)   {

                if (_isCached || _isText && cache != null) {
                    rowStore.release(row.getPos());
                }
            }

            public void commitRowToStore(Row row) {

                if (_isText && cache != null) {
                    rowStore.commit(row);
                }
            }

            public void indexRow(Session session, Row row)   {

                int i = 0;

                try {
                    for (; i < indexList.Length; i++) {
                        indexList[i].insert(session, row, i);
                    }
                } catch (HsqlException e) {

                    // unique index violation - rollback insert
                    for (--i; i >= 0; i--) {
                        Node n = row.getNode(i);

                        indexList[i].delete(session, n);
                    }

                    row.delete();
                    removeRowFromStore(row);

                    throw e;
                }
            }

            /**
             *
             */
            public void clearAllRows(Session session) {

                for (int i = 0; i < indexList.Length; i++) {
                    indexList[i].clearAll(session);
                }

                if (!_isTemp) {
                    identitySequence.reset();
                    rowIdSequence.reset();
                }
            }

            /** @todo -- release the rows */
            public virtual void drop() { }

            public bool isWritable() {
                return !isDataReadOnly() && !database.databaseReadOnly
                       && !(database.isFilesReadOnly() && (_isCached || _isText));
            }

            /**
             * Returns the catalog name or null, depending on a database property.
             */
            public string getCatalogName() {

                // PRE: database is never null
                return database.getProperties().isPropertyTrue("hsqldb.catalogs")
                       ? database.getURI()
                       : null;
            }

            /**
             * Returns the schema name.
             */
            public string getSchemaName()
            {
                return tableName.schema.name;
            }

            public int getRowCount(Session session)   {
                return getPrimaryIndex().size(session);
            }

            /**
             * Necessary when over int.MaxValue Row objects have been generated
             * for a memory table.
             */
            public void resetRowId(Session session)   {

                if (_isCached) {
                    return;
                }

                rowIdSequence = new NumberSequence(null, 0, 1, Types.BIGINT);

                RowIterator it = getPrimaryIndex().firstRow(session);;

                while (it.hasNext()) {
                    Row row = it.next();
                    int pos = (int) rowIdSequence.getValue();

                    row.setPos(pos);
                }
            }

            /**
             *  Factory method instantiates a Row based on table type.
             */
            public Row newRow(Object[] o)   {

                Row row;

                try {
                    if (_isMemory) {
                        row = new Row(this, o);

                        int pos = (int) rowIdSequence.getValue();

                        row.setPos(pos);
                    } else {
                        row = CachedRow.newCachedRow(this, o);

                        rowStore.add(row);
                    }
                } catch ( System.IO.IOException e) {
                    throw new HsqlException(
                        e, Trace.getMessage(Trace.GENERAL_IO_ERROR),
                        Trace.GENERAL_IO_ERROR);
                }

                return row;
            }

            public Row restoreRow(Row oldrow)   {

                Row row;

                try {
                    if (_isMemory) {
                        row = new Row(this, oldrow.oData);

                        row.setPos(oldrow.getPos());
                    } else {
                        row = CachedRow.newCachedRow(this, oldrow.oData);

                        row.setStorageSize(oldrow.getStorageSize());
                        row.setPos(oldrow.getPos());
                        rowStore.restore(row);
                    }
                }
                catch (System.IO.IOException e)
                {
                    throw new HsqlException(
                        e, Trace.getMessage(Trace.GENERAL_IO_ERROR),
                        Trace.GENERAL_IO_ERROR);
                }

                return row;
            }

            public class RowStore : PersistentStore {

                private Table _o;

                public RowStore(Table _o)
                {
                    this._o = _o;
                }

                public CachedObject get(int i) {

                    try {
                        return _o.cache.get(i, this, false);
                    } catch (HsqlException) {
                        return null;
                    }
                }

                public CachedObject getKeep(int i) {

                    try {
                        return _o.cache.get(i, this, true);
                    } catch (HsqlException) {
                        return null;
                    }
                }

                public int getStorageSize(int i) {

                    try {
                        return _o.cache.get(i, this, false).getStorageSize();
                    } catch (HsqlException ) {
                        return 0;
                    }
                }

                public void add(CachedObject row)   {
                    _o.cache.add(row);
                }

                public void restore(CachedObject row)   {
                    _o.cache.restore(row);
                }

                public CachedObject get(RowInputInterface _in) {

                    try {
                        if (_o._isText)
                        {
                            return new CachedDataRow(_o, _in);
                        }

                        CachedObject row = new CachedRow(_o, _in);

                        return row;
                    } catch (HsqlException) {
                        return null;
                    } catch (System.IO.IOException) {
                        return null;
                    }
                }

                public CachedObject getNewInstance(int size) {
                    return null;
                }

                public void remove(int i) {

                    try {
                        _o.cache.remove(i, this);
                    }
                    catch (System.IO.IOException) { }
                }

                public void removePersistence(int i) {

                    try {
                        _o.cache.removePersistence(i, this);
                    }
                    catch (System.IO.IOException)
                    {

                        //
                    }
                }

                public void release(int i) {
                    _o.cache.release(i);
                }

                public void commit(CachedObject row) {

                    try {
                        if (_o._isText) {
                            _o.cache.saveRow(row);
                        }
                    }
                    catch (System.IO.IOException)
                    {

                        //
                    }
                }
            }
    }
}
